﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using ExcelApi;
using NetOffice.ExcelApi;
using ExcelDna.Integration;
using NetOffice.ExcelApi.Enums;
using System.Diagnostics;

namespace ExcelComparator
{
    public partial class FrmCompare : Form
    {
        NetOffice.ExcelApi.Application app;
        Worksheet srcSheet;
        Worksheet destSheet;
        object[,] srcValues;
        Range srcRange;
        object[,] destValues;
        bool[,] changedValues;
        Range destRange;
        System.Windows.Forms.Label lblColor;

        //Creamos un delegate para comparar las strings de acuerdo a los parametros
        private delegate bool CompareString(String x, String y);

        public FrmCompare()
        {
            InitializeComponent();
            //LateBindingApi.Core.Factory.Initialize();
            app = new NetOffice.ExcelApi.Application(null, ExcelDnaUtil.Application);

            // Shutdown the painting of the ListBox as items are added.
            lstSheetsSource.BeginUpdate();
            lstSheetsDest.BeginUpdate();

            foreach (NetOffice.ExcelApi.Worksheet pp in app.Sheets)
            {
                lstSheetsSource.Items.Add(pp.Name);
                lstSheetsDest.Items.Add(pp.Name);
            }

            // Allow the ListBox to repaint and display the new items.
            lstSheetsSource.EndUpdate();
            lstSheetsDest.EndUpdate();

        }


        private void lstSheetsSource_SelectedIndexChanged(object sender, System.EventArgs e)
        {
            // Get the currently selected item in the ListBox. 
            if (lstSheetsSource.SelectedIndex >= 0)
            {
                string curItem = lstSheetsSource.SelectedItem.ToString();

                if (lstSheetsDest.SelectedIndex >= 0)
                {
                    if (lstSheetsDest.SelectedItem.ToString().Equals(curItem))
                        lstSheetsDest.SetSelected(0, false);
                }
            }
        }
    
        private void lstSheetsDest_SelectedIndexChanged(object sender, System.EventArgs e)
        {
            if (lstSheetsDest.SelectedIndex >= 0)
            {
                // Get the currently selected item in the ListBox. 
                string curItem = lstSheetsDest.SelectedItem.ToString();


                if (lstSheetsSource.SelectedIndex >= 0)
                {
                    if (lstSheetsSource.SelectedItem.ToString().Equals(curItem))
                        lstSheetsSource.SetSelected(0, false);
                }
            }
        }

        private void btnSelectCells_Click(object sender, EventArgs e)
        {
            if (lstSheetsDest.SelectedIndex >= 0 && lstSheetsSource.SelectedIndex >= 0)
            {
                this.Visible = false;

                srcSheet = (Worksheet) app.Worksheets[lstSheetsSource.SelectedItem.ToString()];
                destSheet = (Worksheet)app.Worksheets[lstSheetsDest.SelectedItem.ToString()];

                srcSheet.Select();

                srcRange = (Range) app.InputBox("Ingrese las celdas a comparar", "Selección del rango", "A1:A1", Type.Missing,
                            Type.Missing, Type.Missing, Type.Missing, 8);

                if (srcRange.Count > 1)
                {
                    //srcValues = (object[,])srcRange.Value;

                    destRange = destSheet.Range(srcRange.Address);
                    //destValues = (object[,])destRange.Value;

                    txtCeldas.Text = srcRange.Address;
                }
                else
                {
                    MessageBox.Show("Seleccione más de una celda");
                }

                this.Visible = true;
            }
            else
            {
                MessageBox.Show("Debe elegir las hojas a comparar", "Error");
            }
        }

        private void btnCompare_Click(object sender, EventArgs e)
        {
            if (String.IsNullOrEmpty(txtCeldas.Text))
            {
                MessageBox.Show("Debe seleccionar un rango primero");
            }
            else
            {
                int row;
                bool equal = true;
                int col;

                try
                {
                    srcValues = (object[,])srcRange.Value;
                    destValues = (object[,])destRange.Value;

                    row = srcValues.GetUpperBound(0);
                    col = srcValues.GetUpperBound(1);

                    changedValues = new bool[row, col];

                    CompareString comp = getCompareStringDelegate();

                    for (int i = 1; i <= row; i++)
                    {
                        for (int j = 1; j <= col; j++)
                        {
                            if (srcValues[i, j] != destValues[i, j])
                            {
                                if (srcValues[i, j] != null && destValues[i, j] != null)
                                {
                                    if (!comp(srcValues[i, j].ToString(), destValues[i, j].ToString()))
                                    {
                                        equal = false;
                                        //srcValues[i, j] = destValues[i, j];
                                        changedValues.SetValue(true, i - 1, j - 1);
                                    }
                                }
                                else if (srcValues[i, j] != null || destValues[i, j] != null)
                                {
                                    equal = false;
                                    //srcValues[i, j] = destValues[i, j];
                                    changedValues.SetValue(true, i - 1, j - 1);
                                }
                            }
                        }
                    }


                    if (!equal)
                    {
                        //app.Worksheets.Contains["Compare"];
                        Worksheet compare = null;
                        int z = 0;

                        while (compare == null)
                        {
                            if (checkSheet("Compare" + z))
                            {
                                z++;
                            }
                            else
                            {
                                compare = (Worksheet)app.Worksheets.Add();
                                compare.Name = "Compare" + z;
                            }
                        }

                        Range src = compare.Range(compare.Cells[2, 2], compare.Cells[row + 1, col + 1]);
                        src.Value = srcValues;
                        Color color = btnColor.BackColor;
                        long lColor = color.B * 65536 + color.G * 256 + color.R;
                        bool rowChanged;

                        for (int i = 0; i < row; i++)
                        {
                            rowChanged = false;
                            for (int j = 0; j < col; j++)
                            {
                                if (changedValues[i, j])
                                {
                                    src.Cells[i + 1, j + 1].Interior.Color = lColor;
                                    rowChanged = true;
                                }
                            }
                            if (rowChanged)
                            {
                                compare.Cells[i + 2, 1].Value = "Diferencias";
                            }
                        }

                        Range dest = compare.Range(compare.Cells[2, col + 3], compare.Cells[row + 1, col * 2 + 2]);
                        dest.Value = destValues;
                        for (int i = 0; i < row; i++)
                        {
                            for (int j = 0; j < col; j++)
                            {
                                if (changedValues[i, j])
                                {
                                    dest.Cells[i + 1, j + 1].Interior.Color = lColor;
                                }
                            }
                        }

                        Range inter = compare.Range(compare.Cells[2, col + 2], compare.Cells[row + 1, col + 2]);
                        for (int i = 0; i < row; i++)
                        {
                            inter.Cells[i + 1, 1].Interior.ColorIndex = 1;
                        }

                        compare.Columns[inter.Column].ColumnWidth = 2.4;
                        compare.Select();
                        
                    }
                    else
                    {
                        MessageBox.Show("Las dos hojas son idénticas", "Comparación", MessageBoxButtons.OK);
                    }
                }
                catch (Exception ex)
                {
                    Debug.Write(ex.StackTrace);
                }
            }
        }

        private bool compareIgnoreCaseDiacritic(String x, String y)
        {
            return (String.Compare(x, y, System.Globalization.CultureInfo.CurrentCulture, System.Globalization.CompareOptions.IgnoreCase | System.Globalization.CompareOptions.IgnoreNonSpace) == 0);
        }

        private bool compare(String x, String y)
        {
            return x.Equals(y,StringComparison.Ordinal);
        }

        private bool compareIgnoreCase(String x, String y)
        {
            return x.Equals(y, StringComparison.OrdinalIgnoreCase);
        }

        private bool compareIgnoreDiacritic(String x, String y)
        {
            return (String.Compare(x, y, System.Globalization.CultureInfo.CurrentCulture, System.Globalization.CompareOptions.IgnoreNonSpace) == 0);
        }

        private CompareString getCompareStringDelegate()
        {
            CompareString comp = null;

            if (chkCase.Checked && chkDiacritic.Checked)
            {
                comp = compareIgnoreCaseDiacritic;
            }
            else if (chkCase.Checked && !chkDiacritic.Checked)
            {
                comp = compareIgnoreCase;
            }
            else if (!chkCase.Checked && chkDiacritic.Checked)
            {
                comp = compareIgnoreDiacritic;
            }
            else if (!chkCase.Checked && !chkDiacritic.Checked)
            {
                comp = compare;
            }

            return comp;
        }

        private bool checkSheet(String sheetName)
        {
            bool exist = false;

            foreach (Worksheet ws in app.Worksheets)
            {
                if (ws.Name.ToString().Equals(sheetName))
                {
                    exist = true;
                }
            }
            return exist;
        }

        private void btnColor_Click(object sender, EventArgs e)
        {
            try
            {
                Color colorBack = btnColor.BackColor;

                this.clrDialog.Color = colorBack;
                this.clrDialog.ShowDialog();

                btnColor.BackColor = this.clrDialog.Color;
            }
            catch (Exception ex)
            {
                Debug.WriteLine(ex.StackTrace);
            }
        }

    }
}
